/*
* DB.java
*/
package iRoomMonitor;

import java.sql.*;
import java.util.HashMap;
import java.util.HashSet;

/**
* Class container for the static API functions exposed to 
* the Embedded Systems team.
* 
* @author Doug Hamilton, Chris Hale
* @version 1.2
*/
public class DB {


    // Database connection information
    private static String username = "";
    private static String password = "";
    private static Connection connection;
    private static boolean debugMode = false;
    // Database interfacing
    private static CallableStatement sensorCall;
    private static CallableStatement dataCall;
    private static CallableStatement dataBackupCall;
    private static CallableStatement batteryCall;
    private static HashSet<Integer> sensorIDs = new HashSet<Integer>();
    private static HashMap<DoorState, String> doorStateMapping = new HashMap<DoorState, String>();
    // Althrough lights are on or off, we're accommodating other possibilities
    // by using a mapping
    private static HashMap<Boolean, String> lightStateMapping = new HashMap<Boolean, String>();


    // Static block to construct state mappings
    static {
        doorStateMapping.put(DoorState.Opening, "opening");
        doorStateMapping.put(DoorState.Closing, "closing");
        doorStateMapping.put(DoorState.Moving, "moving");
        doorStateMapping.put(DoorState.Still, "still");
        lightStateMapping.put(false, "off");
        lightStateMapping.put(true, "on");
    }


    /**
    * Registers a sensor's hardware into the Room Nanny database.  Returns an
    * integer representing the ID for the sensor.  <b>Required for each sensor
    * pushing data</b> before any data may be pushed.  DB.connect must be
    * called <b>before</b> any registerSensor calls.
    * @param sensorID Serial number of the sensor in String format.
    * Ex: 1234.1234.1234.1234
    * @return ID of the sensor that should be passed into the pushData calls.
    * @throws DBException If an error occurs while registering the sensor
    * @see DB#connect(String, String, String, boolean)
    */
    public static int registerSensor(String sensorID) throws DBException {

        // Make sure a connection to the database has been established before continuing
        try {
            if (connection == null || connection.isClosed()) {
                throw new DBException("Not connected to Room Nanny database. Please call connect() first.");
            }
        } catch (Exception e) {
            throw new DBException("An unknown error occurred while testing database connection.");
        }

        int id = -1;
        try {
            sensorCall.setString(1,sensorID);
            sensorCall.execute();
            id = sensorCall.getInt(2);
            sensorIDs.add(id);
        } catch (SQLException e) {
            throw new DBException("Error registering sensor: " + e);
        }

        return id;
    }


    /**
    * Establishes a connection to the database. Must be called before SendData
    * can be used.
    * @param databaseAddress The address to the database including database
    * name.  Should normally be <b>cs340.pugetsound.edu/RoomNanny</b>
    * @param debug If set to true, the data pushed will be thrown in a separate
    * database used for debugging purposes.  This debug database may have fake
    * and/or inconsistent data. Use when testing your application.
    * @throws DBException If a database connection error occurs.
    */
    public static void connect(String databaseAddress, String user, String pw,
                               boolean debug) throws DBException {

        // Sets database user information
        username = user;
        password = pw;

        // Determine if using the Debug database or the stable database
        debugMode = debug;
        if (debugMode) {
            // Append "Debug" on the end of the database name for debug access
            databaseAddress += "Debug";
        }

        // Attach protocol
        databaseAddress = "jdbc:mysql://" + databaseAddress;

        // Attempt to load MySQL JDBC driver
        // (The JDBC driver should be included within the ES API jar package)
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new DBException("Error loading MySQL JDBC driver. Is the MySQL JDBC jar file in the classpath? Is it missing?");
        }

        // Attempt to connect to the MySQL server
        try {
            connection = DriverManager.getConnection(databaseAddress,
                                                     username, password);
        } catch (Exception e) {
            throw new DBException("Error connecting to database: " + e.toString());
        }

        // Double check database connection
        try {
            if (!connection.isClosed()) {
                System.out.println("Successfully connected to MySQL server");
            } else {
                throw new DBException("Error connecting to Room Nanny MySQL server. Is the server down?");
            }
        } catch (Exception e) {
            throw new DBException("Error testing database connection.");
        }

        // Prepare callable statements (i.e. the database's stored procedures)
        try {
            // Link to the call
            sensorCall = connection.prepareCall("{CALL registerSensor(?,?)}");
            // Register the output parameter
            sensorCall.registerOutParameter(2, Types.INTEGER);
            // Link to pushData call
            dataCall = connection.prepareCall("{CALL pushState(?,?,?,?)}");
            // Link to pushSpecific call
            dataBackupCall = connection.prepareCall("{CALL pushBackupState(?,?,?,?,?)}");
            //dataBackupCall.registerOutParameter(2, Types.TIMESTAMP);
            // Link to battery call
            batteryCall = connection.prepareCall("{CALL updateBatteryStatus(?,?)}");
        } catch (SQLException e) {
            throw new DBException("SQL error while preparing calls: "+e);
        }

    }


    /**
    * Sends the data provided in the parameters to be stored in the database.
    * An active database connection must be established prior to using this
    * method. Use connect() to connect to the database.
    * Also, the sensorID must be registered previously using the registerSensor
    * method.
    * The database uses the time at which its stored procedure is invoked. If
    * this is not appropriate and you need to push data that was recorded at an
    * earlier time, use pushBackupData() instead.
    * 
    * @param sensorID ID number assigned from the registerSensor method
    * @param temperature The current temperature.
    * @param doorState The current state of the door.
    * @param lightOn The current light state.
    * @throws DBException If there is an error transmitting data.
    * @see DB#connect(String, String, String, boolean)
    * @see DB#registerSensor(String)
    * @see DB#pushBackupData(int, String, float, DoorState, boolean)
    */
    public static void pushData(int sensorID, float temperature, DoorState doorState,
                                boolean lightOn) throws DBException {

        // First, ensure the connection is active
        try {
            if (connection == null || connection.isClosed()) {
                throw new DBException("Not connected to the database. Please call connect() first.");
            }
        } catch (Exception e) {
            throw new DBException("An unknown error occurred while testing database connection.");
        }

        // Ensure the sensor is registered
        if (!sensorIDs.contains(sensorID))
        {
            throw new DBException("Error: The sensor ID is not registered, please register the sensor using registerSensor");
        }

        // NOTE: MySQL will automagically round off our temperature float the tenth degree!
        // Life is sweet.
        try {
            // NOTE: Parameter index is NOT zero based
            dataCall.setInt(1, sensorID);
            dataCall.setFloat(2, temperature);
            dataCall.setString(3, doorStateMapping.get(doorState));
            dataCall.setString(4, lightStateMapping.get(lightOn));
            dataCall.execute();
            System.out.println("Data successfully stored in database");
        } catch (SQLException e) {
            throw new DBException("SQL error:  " + e.toString());
        }
    }
    
    
    /**
    * Sends the data provided in the parameters to be stored in the database
    * recorded at a specific time. The intent of this method is to store data
    * that had been collected when the database was down or otherwise
    * unavailable. The time parameter allows data to be pushed at the time
    * it was recorded instead of the time it was pushed to the database (time
    * explicit data).
    * An active database connection must be established prior to using this
    * method. Use connect() to connect to the database.
    * Also, the sensorID must be registered previously using the registerSensor
    * method.
    * 
    * @param sensorID ID number assigned from the registerSensor method
    * @param time The time at which this data had been recorded
    * @param temperature The current temperature.
    * @param doorState The current state of the door.
    * @param lightOn The current light state.
    * @throws DBException If there is an error transmitting data.
    * @see DB#connect(String, String, String, boolean)
    * @see DB#registerSensor(String)
    */
    public static void pushBackupData(int sensorID, String time, float temperature,
		                        DoorState doorState, boolean lightOn) throws DBException {

        // First, ensure the connection is active
        try {
            if (connection == null || connection.isClosed()) {
                throw new DBException("Not connected to the database. Please call connect() first.");
            }
        } catch (Exception e) {
            throw new DBException("An unknown error occurred while testing database connection.");
        }

        // Ensure the sensor is registered
        if (!sensorIDs.contains(sensorID))
        {
            throw new DBException("Error: The sensor ID is not registered, please register the sensor using registerSensor");
        }

        // NOTE: MySQL will automagically round off our temperature float the tenth degree!
        // Life is sweet.
        try {
            // NOTE: Parameter index is NOT zero based
            dataBackupCall.setInt(1, sensorID);
            dataBackupCall.setTimestamp(2, Timestamp.valueOf(time));
            dataBackupCall.setFloat(3, temperature);
            dataBackupCall.setString(4, doorStateMapping.get(doorState));
            dataBackupCall.setString(5, lightStateMapping.get(lightOn));
            dataBackupCall.execute();
            System.out.println("Backup (time explicit) data successfully stored in database");
        } catch (SQLException e) {
            throw new DBException("SQL error:  " + e.toString());
        }
    }


    /**
    * Sends the latest battery usage status to be stored in the database for a
    * particular sensor. An active database connection must be established
    * prior to using this method. Use connect() to connect to the database.
    * Also, the sensorID must be registered previously using the registerSensor
    * method.
    * 
    * @param sensorID ID number assigned from the registerSensor method
    * @param percentage The current battery usage percentage. 100 = full, 0 = empty
    * @throws DBException If there is an error transmitting data.
    * @see DB#connect(String, String, String, boolean)
    * @see DB#registerSensor(String)
    */
    public static void updateBatteryStatus(int sensorID, int percentage) throws DBException {

        // First, ensure the connection is active
        try {
            if (connection == null || connection.isClosed()) {
                throw new DBException("Not connected to the database. Please call connect() first.");
            }
        } catch (Exception e) {
            throw new DBException("An unknown error occurred while testing database connection.");
        }

        // Ensure the sensor is registered
        if (!sensorIDs.contains(sensorID))
        {
            throw new DBException("Error: The sensor ID is not registered, please register the sensor using registerSensor");
        }
        
        // Ensure percentage is within range.
        if (percentage < 0 || percentage > 100)
        {
            throw new DBException("Given percentage is not in proper range. Range must be: [0,100]");
        }

        try {
            // NOTE: Parameter index is NOT zero based
            batteryCall.setInt(1, sensorID);
            batteryCall.setInt(2, percentage);
            batteryCall.execute();
            System.out.println("Successfully updated battery status for sensor");
        } catch (SQLException e) {
            throw new DBException("SQL error:  " + e.toString());
        }
    }


    /**
    * Closes the active connection to the database.
    * 
    * @throws DBException If the database connection is already closed.
    * @see DB#connect(String, String, String, boolean)
    */
    public static void disconnect() throws DBException {

    // Check if the database connection is already closed.
    try {
        if (connection == null || connection.isClosed()) {
            throw new DBException("Already disconnected from the database.");
        }
    } catch (Exception e) {
        throw new DBException("An unknown error occurred while testing database connection.");
    }

    // Attempt to close connection
    try {
        connection.close();
        System.out.println("Disconnected from MySQL server.");
    } catch (Exception e) {
        throw new DBException("An error occurred while disconnecting from the database:\n"
                              + e.toString());
        }
    }

}
